package com.hnkywl.sts.dao.jhgl;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.jhgl.Xsxy;
import com.hnkywl.sts.entity.jhgl.Xy;
import com.hnkywl.sts.service.sys.YgService;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-4-2
 * Time: 下午2:57
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class XyDao extends SimpleHibernateTemplate<Xy, Long> {

    public Page<Xy> findXyList(String xyhm, Long ssgsId, Long gysId, Long khId, String spzt, int pageNo, int pageSize) {
        Page<Xy> page = new Page<Xy>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .setProjection(Projections.projectionList()
                        .add(Projections.sqlProjection("type as type", new String[]{"type"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("id").as("id"))
                        .add(Projections.sqlProjection("xyzt as xyzt", new String[]{"xyzt"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("xyhm as xyhm", new String[]{"xyhm"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("kssj").as("kssj"))
                        .add(Property.forName("jssj").as("jssj"))
                        .add(Property.forName("nxyl").as("nxyl"))
                        .add(Property.forName("yxyl").as("yxyl"))
                        .add(Property.forName("yzxl").as("yzxl"))
                        .add(Property.forName("createUser.xm").as("zdr"))
                        .add(Property.forName("processInstanceId").as("processInstanceId"))
                        .add(Property.forName("bz").as("bz"))).setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(xyhm))
            criteria.add(Property.forName("xyhm").like(xyhm, MatchMode.START));
        if (StringUtils.isNotEmpty(spzt))
            criteria.add(Property.forName("xyzt").eq(spzt));
        if (gysId != null)
            criteria.add(Restrictions.eq("gys.id", gysId));
        if (ssgsId != null)
            criteria.add(Restrictions.eq("nbgs.id", ssgsId));
        if (khId != null)
            criteria.add(Restrictions.eq("kh.id", khId));
        RegionDao.createDataRegion(criteria);
        return this.findByCriteria(page, criteria);
    }


    public Page<Map> findXyList(List<String> types, String tplx,String tcgs,Long ssgsId, String spzt, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        List<String> conditions = new ArrayList<String>();
        if (!CollectionUtils.isEmpty(types))
            conditions.add(" x.type in (:types) ");
        if (StringUtils.isNotEmpty(tplx))
            conditions.add(" x.jyms_id is not null");
        if (StringUtils.isNotEmpty(tcgs))
            conditions.add(" j.tcgs_id is not null");
        if (ssgsId != null)
            conditions.add(" x.nbgs_id=:ssgsId ");
        if (spzt != null)
            conditions.add(" x.xyzt=:spzt ");
        String sqlColumn = " select x.type \"type\",x.id \"id\",x.xyhm \"xyhm\",x.rzlx \"rzlx\",x.kssj \"kssj\",x.jssj \"jssj\",x.date_created \"zdsj\",x.gys_id \"gys_id\",q.qc \"gys_qc\"," +
                " x.kh_id \"kh_id\",k.qc \"kh_qc\",x.dbf_id \"dbf_id\",d.qc \"dbf_qc\",x.nxyl \"nxyl\",x.yxyl \"yxyl\",x.nbgs_id \"nbgs_id\",ng.qc \"nbgs_qc\"," +
                " x.zdr_id \"zdr_id\",y.xm \"zdr_xm\",x.bz \"bz\",tg.id \"tcgs_id\",tg.qc \"tcgs_qc\",tb.id \"tcbm_id\",tb.qc \"tcbm_qc\",zd.id \"tcfs_id\"," +
                " zd.z \"tcfs_z\",x.lj_id \"lj_id\",lj.mc \"lj_mc\",lj.ll \"lj_ll\",lj.kxg \"lj_kxg\" ";
        String sqlCounter = " select count(1) ";
        String sqlJoinTable = " from t_biz_xy x" +
                " left join t_sys_gsjg q on q.type='qyjg' and x.gys_id=q.id and q.deleted=0" +
                " left join t_sys_gsjg k on k.type='qyjg' and x.kh_id=k.id and k.deleted=0" +
                " left join t_sys_gsjg d on d.type='qyjg' and x.dbf_id=d.id and d.deleted=0" +
                " left join t_sys_gsjg ng on ng.type='nbgs' and x.nbgs_id=ng.id and ng.deleted=0" +
                " left join t_sys_yg y on x.zdr_id=y.id and y.deleted=0" +
                " left join t_sys_jyms j on x.jyms_id=j.id and j.deleted=0" +
                " left join t_sys_gsjg tg on tg.type='nbgs' and j.tcgs_id=tg.id and tg.deleted=0" +
                " left join t_sys_gsjg tb on tb.type='nbbm' and j.tcbm_id=tb.id and tb.deleted=0" +
                " left join t_sys_sjzdz zd on j.sjzdz_tcfs_id=zd.id and zd.deleted=0" +
                " left join t_sys_lj lj on x.lj_id=lj.id and lj.deleted=0";
        String sqlCondition = " where x.deleted=0 " + (conditions.isEmpty() ? "" : " and " + StringUtils.join(conditions, " and "));
        SQLQuery query = createSQLQuery(sqlCounter + sqlJoinTable + sqlCondition + " order by x.date_created desc");
        if (!CollectionUtils.isEmpty(types))
            query.setParameterList("types", types);
        if (ssgsId != null)
            query.setLong("ssgsId", ssgsId);
        if (spzt != null)
            query.setString("spzt", spzt);
        BigDecimal total = (BigDecimal) query.uniqueResult();
        if (total.intValue() > 0) {
            page.setTotalCount(total.intValue());
            query = createSQLQuery(sqlColumn + sqlJoinTable + sqlCondition + " order by x.date_created desc");
            if (!CollectionUtils.isEmpty(types))
                query.setParameterList("types", types);
            if (ssgsId != null)
                query.setLong("ssgsId", ssgsId);
            if (spzt != null)
                query.setString("spzt", spzt);
            List<Map> list = query.setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    public Page findAllSimpleXsXy(int pageNo, int pageSize) {
        Page<Xy> page = new Page<Xy>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(Xsxy.class)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .createAlias("lj", "lj", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("xyhm").as("xyhm"))
                        .add(Property.forName("lj.id").as("lj_id"))
                        .add(Property.forName("lj.mc").as("lj_mc"))
                        .add(Property.forName("lj.kxg").as("lj_kxg"))
                        .add(Property.forName("kh.id").as("kh_id"))
                        .add(Property.forName("kh.qc").as("kh_qc"))
                        .add(Property.forName("rzlx").as("rzlx"))
                        .add(Property.forName("nxyl").as("nxyl"))
                        .add(Property.forName("yxyl").as("yxyl"))
                        .add(Property.forName("fkfs.id").as("fkfs_id"))
                        .add(Property.forName("jhfs").as("jhfs"))
                        .add(Property.forName("hkqx").as("hkqx"))
                        .add(Property.forName("hkjsfs.id").as("hkjsfs_id"))
                        .add(Property.forName("zdsj").as("zdsj")))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        criteria.add(Restrictions.eq("xyzt","ysp"));
        RegionDao.createDataRegion(criteria);
        return this.findByCriteria(page, criteria);
    }

    /**
     * 首页协议总数
     *
     * @param xyzt
     * @return
     */
    public List findXyList(String xyzt,String xylx) {
        String sql = " select count(xyzt) from t_biz_xy a where a.nbgs_id =? and deleted = '0' and xyzt = ? and a.type in (?)";
        return this.findBySQL(sql, YgService.getCurrentYgZgGsId(), xyzt,xylx);
    }
}
